package org.example.interceptor;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.script.ScriptUtil;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.*;
import com.alibaba.druid.sql.ast.expr.*;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.visitor.SQLASTVisitorAdapter;

import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.context.ApplicationContext;
import org.springframework.core.annotation.AnnotationUtils;
import org.springframework.stereotype.Component;
import org.example.common.RouteCacheManage;
import org.example.common.Sharding;
import org.example.config.RouterConfig;
import org.example.event.SqlChangeEvent;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Component
@Intercepts({
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class,
                ResultHandler.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class,
                ResultHandler.class, CacheKey.class, BoundSql.class}),
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
})
public class SqlInterceptor implements Interceptor {

    DefaultObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
    DefaultObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
    DefaultReflectorFactory DEFAULT_REFLECTOR_FACTORY = new DefaultReflectorFactory();

    @Resource
    private ApplicationContext context;

    private Map<String, Sharding> tblNameToBean;

    @Resource
    private RouterConfig routerConfig;
    @Resource
    private DataSource dataSource;
    @Resource
    private RouteCacheManage routeCacheManage;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];

        Object parameterObj = args[1];
        SqlSource sqlSource = ms.getSqlSource();
        BoundSql boundSql = sqlSource.getBoundSql(parameterObj);
        String sql = boundSql.getSql();

        sql = rewriteSql(sql, ms.getId());
        sql = sql.replaceAll("\n", " ");

        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        filter(parameterMappings, sql);
        List<ParameterMapping> parameterMappingsCopy = new ArrayList<>(parameterMappings);
        SqlCommandType sqlCommandType = ms.getSqlCommandType();
        Map<String, List<String>> needSplitTbl = new HashMap<>();// 逻辑表->实际路由表List
        if (sqlCommandType == SqlCommandType.SELECT) {
            Map<String, String> tblNameAliasMap = new HashMap<>();
            Map<String, SQLExpr> tblNameWhereMap = new HashMap<>();
            MySqlStatementParser parser = new MySqlStatementParser(sql);
            SQLSelectStatement selectStatement = (SQLSelectStatement) parser.parseStatement();
            MySqlSelectQueryBlock query = (MySqlSelectQueryBlock) selectStatement.getSelect().getQuery();

            processSQLTableSource(query, tblNameAliasMap, tblNameWhereMap);

            Set<String> tblNames = tblNameAliasMap.keySet();
            for (String tblName : tblNames) {
                Sharding joinAnnotation = findShardByTblName(tblName);
                if (joinAnnotation == null) {
                    continue;
                }
                SQLExpr where = query.getWhere();
                if (where == null) {
                    where = tblNameWhereMap.get(tblName);
                }
                List<String> fields = Arrays.asList(joinAnnotation.field());
                String tblAlias = tblNameAliasMap.get(tblName);
                Map<String, Object> fieldValMap = new HashMap<>();
                // 下面的方法还有很大的改进空间
                findFieldValFromParam(parameterObj, parameterMappingsCopy, fields, fieldValMap);
                findFieldValFromSqlProp(parameterMappingsCopy, fields, tblAlias, where, sql,
                        parameterObj, fieldValMap, needSplitTbl, tblName);
                if (needSplitTbl.isEmpty()) {
                    needSplitTbl.put(tblName, new ArrayList<>());
                }
            }
            sql = genSql(sql, parameterMappings, parameterMappingsCopy, needSplitTbl, 0, true);
        }

        // 单表 update
        if (sqlCommandType == SqlCommandType.UPDATE) {
            MySqlStatementParser parser = new MySqlStatementParser(sql);
            MySqlUpdateStatement updateStatement = (MySqlUpdateStatement) parser.parseStatement();
            SQLTableSource tableSource = updateStatement.getTableSource();
            SQLName tableName = updateStatement.getTableName();
            String tblName = tableName.getSimpleName();

            Sharding annotation = findShardByTblName(tblName);
            if (annotation != null) {
                String tblAlias = "";
                if (StrUtil.isNotBlank(tableSource.getAlias())) {
                    tblAlias = tableSource.getAlias();
                }
                List<String> fields = Arrays.asList(annotation.field());
                SQLExpr where = updateStatement.getWhere();
                Map<String, Object> fieldValMap = new HashMap<>();

                findFieldValFromParam(parameterObj, parameterMappingsCopy, fields, fieldValMap);
                findFieldValFromSqlProp(parameterMappingsCopy, fields, tblAlias, where, sql, parameterObj, fieldValMap
                        , needSplitTbl, tblName);
                parseRoute(needSplitTbl, tblName, fieldValMap, "", "");
                sql = genSql(sql, parameterMappings, parameterMappingsCopy, needSplitTbl, 0, false);
            }
        }
        HashSet<String> interceptorPropSet = new HashSet<>(Arrays.asList("create_time", "complete_time"));

        if (sqlCommandType == SqlCommandType.INSERT) {
            MySqlStatementParser parser = new MySqlStatementParser(sql);
            SQLInsertStatement insertStatement = (SQLInsertStatement) parser.parseStatement();
            String tblName = insertStatement.getTableName().getSimpleName();

            Sharding annotation = findShardByTblName(tblName);
            if (annotation != null) {
                List<String> fields = Arrays.asList(annotation.field());
                int length = annotation.length();

                Map<String, Object> fieldValMap = new HashMap<>();
                findFieldValFromParam(parameterObj, parameterMappingsCopy, fields, fieldValMap);
                parseRoute(needSplitTbl, tblName, fieldValMap, "", "");
                sql = genSql(sql, parameterMappings, parameterMappingsCopy, needSplitTbl, length, false);
            }
            // 默认只插到一张表
            String targetTbl = "";
            Iterator<List<String>> iterator = needSplitTbl.values().iterator();
            while (iterator.hasNext()) {
                targetTbl = iterator.next().get(0);
            }
            List<SQLExpr> columns = insertStatement.getColumns();
            for (SQLExpr column : columns) {
                if (column instanceof SQLIdentifierExpr) {
                    String columnName = ((SQLIdentifierExpr) column).getName();
                    if (interceptorPropSet.contains(columnName)) {
                        // 检查路由的目标表
                        String shardVal = findShardVal(columnName, parameterMappingsCopy, parameterObj);
                        List<String> targetTblList = routeCacheManage.findTargetTbl(shardVal, columnName);
                        if (!targetTblList.contains(targetTbl)) {
                            try (Connection connection = dataSource.getConnection()) {
                                PreparedStatement ps = connection.prepareStatement("insert into routing_cache(target_tbl,shard_val,shard_col) " +
                                        "   values (?,?,?)");
                                ps.setString(1, targetTbl);
                                ps.setString(2, shardVal);
                                ps.setString(3, columnName);
                                ps.execute();
                            } catch (SQLException e) {
                                throw new RuntimeException(e);
                            }
                        }
                    }
                }
            }
            if (tblName.equalsIgnoreCase("user_3")) { // tbl_mission
                context.publishEvent(SqlChangeEvent.builder().changedTblName(tblName).build());
                System.out.println("发送表修改事件：" + System.currentTimeMillis());
            }
        }

        Object[] queryArgs = invocation.getArgs();
        queryArgs[0] = getMappedStatement(ms, boundSql, sql, parameterMappings);
        return invocation.proceed();
    }

    private static String rewriteSql(String sql, String id) {
        MySqlStatementParser parser = new MySqlStatementParser(sql);
        SQLSelectStatement selectStatement = (SQLSelectStatement) parser.parseStatement();
        MySqlSelectQueryBlock query = (MySqlSelectQueryBlock) selectStatement.getSelect().getQuery();
        query.accept(new SQLASTVisitorAdapter() {

            @Override
            public boolean visit(SQLLimit sqlLimit) {
                SQLExpr offset = sqlLimit.getOffset();
                SQLExpr rowCount = sqlLimit.getRowCount();
                int offsetNum = 0;
                if (offset != null) {
                    offsetNum = (Integer) ((SQLIntegerExpr) offset).getNumber();
                }
                int rowCountNum = (Integer) ((SQLIntegerExpr) rowCount).getNumber();
                // 默认一个SQL 只有一个limit
                LimitHolder.get().put(id, offsetNum + ":" + rowCountNum);
                sqlLimit.setOffset(0);
                sqlLimit.setRowCount(offsetNum + rowCountNum);
                return super.visit(sqlLimit);
            }

            @Override
            public boolean visit(SQLOrderBy orderBy) {
                List<SQLSelectOrderByItem> items = orderBy.getItems();
                Map<String, SQLExpr> map = new HashMap<>();
                for (SQLSelectOrderByItem item : items) {
                    SQLExpr expr = item.getExpr();
                    if (expr instanceof SQLIdentifierExpr) {
                        map.put(((SQLIdentifierExpr) expr).getName(), expr);
                    } else {
                        throw new RuntimeException("未知类型");
                    }
                }
                MySqlSelectQueryBlock parent = SqlUtil.findParent(orderBy);
                List<SQLSelectItem> selectList = parent.getSelectList();
                for (Map.Entry<String, SQLExpr> entry : map.entrySet()) {
                    if (needAddItem(entry, selectList)) {
                        SQLSelectItem sqlSelectItem = new SQLSelectItem();
                        SQLExpr value = entry.getValue();
                        value.setParent(sqlSelectItem);
                        sqlSelectItem.setExpr(value);
                        sqlSelectItem.setAlias(entry.getKey());
                        selectList.add(sqlSelectItem);
                    }
                }
                return super.visit(orderBy);
            }

            @Override
            public boolean visit(SQLSelectGroupByClause groupBy) {
                List<SQLExpr> items = groupBy.getItems();
                SQLExpr sqlExpr = items.get(0);
                MySqlSelectQueryBlock parent = SqlUtil.findParent(sqlExpr);
                List<SQLSelectItem> selectList = parent.getSelectList();
                List<SQLExpr> diff = SqlUtil.diff(selectList, items);
                int i = 0;
                for (SQLExpr item : diff) {
                    // 这里的 item 一般是 SQLIdentity 类型
                    SQLSelectItem sqlSelectItem = new SQLSelectItem();
                    item.setParent(sqlSelectItem);
                    sqlSelectItem.setExpr(item);
                    sqlSelectItem.setAlias(SQLUtils.toMySqlString(item));
                    parent.addSelectItem(sqlSelectItem);
                    i++;
                }
                return super.visit(groupBy);
            }

            @Override
            public boolean visit(SQLAggregateExpr x) {
                SQLExpr param = null;
                String paramName = "";
                MySqlSelectQueryBlock parent = SqlUtil.findParent(x);

                List<SQLSelectItem> selectList = parent.getSelectList();
                List<SQLExpr> collect = selectList.stream().map(SQLSelectItem::getExpr).filter(it -> it instanceof SQLAggregateExpr)
                        .collect(Collectors.toList());
                SQLAggregateExpr count = new SQLAggregateExpr("count");
                SQLAggregateExpr sum = new SQLAggregateExpr("sum");
                boolean hasCount = false, hasSum = false;
                if (CollectionUtil.isNotEmpty(collect)) {
                    for (SQLExpr sqlExpr : collect) {
                        SQLAggregateExpr aggregateExpr = (SQLAggregateExpr) sqlExpr;
                        if (aggregateExpr.getMethodName().equals("avg")) {
                            param = aggregateExpr.getArguments().get(0);
                            paramName = ((SQLIdentifierExpr) aggregateExpr.getArguments().get(0)).getName();
                        }
                    }
                    if (StrUtil.isBlank(paramName)) {
                        return true;
                    }
                    for (SQLExpr sqlExpr : collect) {
                        SQLAggregateExpr aggregateExpr = (SQLAggregateExpr) sqlExpr;
                        SQLExpr sqlIdentify = aggregateExpr.getArguments().get(0);
                        String name = "";
                        if (sqlIdentify instanceof SQLIdentifierExpr) {
                            name = ((SQLIdentifierExpr) sqlIdentify).getName();
                        }
                        if (aggregateExpr.getMethodName().equals("count") && name.equals(paramName)) {
                            hasCount = true;
                        }
                        if (aggregateExpr.getMethodName().equals("sum") && name.equals(paramName)) {
                            hasSum = true;
                        }
                    }
                }
                if (hasCount && hasSum) {
                    return true;
                }
                SQLSelectItem sumExpr = new SQLSelectItem();

                sum.setParent(sumExpr);
                sum.addArgument(param);
                sumExpr.setParent(parent);
                sumExpr.setExpr(sum);
                sumExpr.setAlias("plugin_generate_sum");
                parent.addSelectItem(sumExpr);

                SQLSelectItem countExpr = new SQLSelectItem();
                count.setParent(countExpr);
                count.addArgument(param);
                countExpr.setExpr(count);
                countExpr.setParent(parent);
                countExpr.setAlias("plugin_generate_count");
                parent.addSelectItem(countExpr);
                return super.visit(x);
            }
        });
        sql = SQLUtils.toMySqlString(query);
        System.out.println("改写后的sql：" + sql);
        return sql;
    }

    private static boolean needAddItem(Map.Entry<String, SQLExpr> entry, List<SQLSelectItem> selectList) {
        for (SQLSelectItem selectItem : selectList) {
            String alias = selectItem.getAlias();
            SQLExpr expr = selectItem.getExpr();
            if (StrUtil.isNotBlank(alias) && entry.getKey().equalsIgnoreCase(alias)) {
                return false;
            }
            if (expr.toString().equalsIgnoreCase(entry.getKey())) {
                return false;
            }
        }
        return true;
    }


    private void processSQLTableSource(MySqlSelectQueryBlock queryBlock, Map<String, String> tblNameMap, Map<String, SQLExpr> tblNameWhereMap) {
        SQLTableSource tableSource = queryBlock.getFrom();
        if (tableSource instanceof SQLJoinTableSource) {
            processSQLJoinTableSource(((SQLJoinTableSource) tableSource), tblNameMap, tblNameWhereMap, queryBlock);
        } else if (tableSource instanceof SQLExprTableSource) {
            SQLExprTableSource source = (SQLExprTableSource) tableSource;
            tblNameMap.put(source.getTableName(), source.getAlias() != null ? source.getAlias() : "");
            tblNameWhereMap.put(source.getTableName(), queryBlock.getWhere());
        } else if (tableSource instanceof SQLSubqueryTableSource) {
            MySqlSelectQueryBlock query = (MySqlSelectQueryBlock) ((SQLSubqueryTableSource) tableSource).getSelect().getQuery();
            processSQLTableSource(query, tblNameMap, tblNameWhereMap);
        } else {
            throw new RuntimeException("找不到表名");
        }
    }

    private MappedStatement getMappedStatement(MappedStatement ms, BoundSql boundSql, String sql, List<ParameterMapping> parameterMappings) {
        BoundSql newBoundSql = new BoundSql(ms.getConfiguration(), sql,
                parameterMappings, boundSql.getParameterObject());
        for (ParameterMapping mapping : boundSql.getParameterMappings()) {
            String prop = mapping.getProperty();
            if (boundSql.hasAdditionalParameter(prop)) {
                newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
            }
        }
        MappedStatement newMs = newMappedStatement(ms, new BoundSqlSqlSource(newBoundSql));
        return newMs;
    }

    private void filter(List<ParameterMapping> parameterMappings, String sql) {
        int count = getPlaceSymbolCnt(sql), j = 0;
        Iterator<ParameterMapping> iterator = parameterMappings.iterator();
        while (iterator.hasNext()) {
            ParameterMapping next = iterator.next();
            if (j >= count) {
                iterator.remove();
            }
            j++;
        }
    }

    private static void processSQLJoinTableSource(SQLJoinTableSource tableSource, Map<String, String> tblNameMap,
                                                  Map<String, SQLExpr> tblNameWhereMap, MySqlSelectQueryBlock queryBlock) {
        SQLExpr where = queryBlock.getWhere();
        if (tableSource.getLeft() instanceof SQLJoinTableSource) {
            processSQLJoinTableSource((SQLJoinTableSource) tableSource.getLeft(), tblNameMap, tblNameWhereMap, queryBlock);
        } else if (tableSource.getLeft() instanceof SQLExprTableSource) {
            SQLExprTableSource left = (SQLExprTableSource) tableSource.getLeft();
            tblNameMap.put(left.getTableName(), StrUtil.isNotBlank(left.getAlias()) ? left.getAlias() : "");
            tblNameWhereMap.put(left.getTableName(), where);
        } else {
            throw new RuntimeException("error occur");
        }
        SQLExprTableSource right = (SQLExprTableSource) tableSource.getRight();
        tblNameMap.put(right.getTableName(), StrUtil.isNotBlank(right.getAlias()) ? right.getAlias() : "");
        tblNameWhereMap.put(right.getTableName(), where);
    }

    private String findShardVal(String columnName, List<ParameterMapping> parameterMappingsCopy, Object parameterObj) {
        String javaProperty = SqlUtil.toJavaProperty(columnName);
        Object res = null;
        if (parameterObj instanceof MapperMethod.ParamMap) {
            if (((MapperMethod.ParamMap<?>) parameterObj).containsKey(columnName)) {
                res = ((MapperMethod.ParamMap<?>) parameterObj).get(columnName);
            } else {
                if (((MapperMethod.ParamMap<?>) parameterObj).containsKey(javaProperty)) {
                    res = ((MapperMethod.ParamMap<?>) parameterObj).get(javaProperty);
                }
            }
        } else {
            MetaObject metaObject = MetaObject.forObject(parameterObj, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_REFLECTOR_FACTORY);
            if (metaObject.hasGetter(columnName)) {
                res = metaObject.getValue(columnName);
            } else if (metaObject.hasGetter(javaProperty)) {
                res = metaObject.getValue(javaProperty);
            }
        }
        if (res instanceof Date) {
            res = DateUtil.format((Date) res, "yyyy");
        }
        return res.toString().substring(0, 4);
    }


    private String genSql(String sql, List<ParameterMapping> parameterMappings,
                          List<ParameterMapping> parameterMappingsCopy,
                          Map<String, List<String>> needSplitTbl, int len, boolean isSelect) {
        // 根据扫表获取路由信息，如果找不到路由会扫全表
        List<AllScan> allScans = getAllScan(needSplitTbl);
        if (!needSplitTbl.isEmpty()) {
            for (Map.Entry<String, List<String>> entry : needSplitTbl.entrySet()) {
                // 找到路由表，直接替换
                if (!entry.getValue().isEmpty()) {
                    List<String> targetTblList = entry.getValue();
                    String sqlCopy = new String(sql);
                    StringBuilder sb = new StringBuilder("(");
                    int i = 0;
                    for (String targetTbl : targetTblList) {
                        String sqlCopyTmp = sqlCopy + " ";
                        sqlCopyTmp = sqlCopyTmp.replace(entry.getKey() + " ", targetTbl + " ");
                        sqlCopyTmp = sqlCopyTmp.replaceAll(entry.getKey() + "\\s*\\(", targetTbl + "(");
                        if (isSelect) {
                            sb.append(sqlCopyTmp).append(") union all (");
                        } else {
                            sb.append(sqlCopyTmp).append(";");
                        }

                        if (i >= 1) {
                            parameterMappings.addAll(parameterMappingsCopy);
                        }
                        i++;
                    }
                    if (isSelect) {
                        sql = sb.substring(0, sb.length() - 13) + ")";
                    } else {
                        sql = sb.substring(1, sb.length() - 1);
                    }

                }
            }
            // 如果 insert 没有找到路由表，默认插入到最后一张表
            boolean isInsert = false;
            try {
                MySqlStatementParser parser = new MySqlStatementParser(sql);
                SQLStatement statement = parser.parseStatementList().get(0);
                if ((statement instanceof MySqlInsertStatement) && allScans.size() > 0) {
                    isInsert = true;
                    String prefix = allScans.get(0).getPrefix();
                    sql = sql.replace(prefix + " ", prefix + "_" + len + " ");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            if (!isInsert) {
                sql = genNewSql(sql, parameterMappings, parameterMappingsCopy, allScans);
            }
        }
        return sql;
    }

    private String genNewSql(String sql, List<ParameterMapping> parameterMappings, List<ParameterMapping> parameterMappingsCopy, List<AllScan> allScans) {
        // 找不到路由表，扫描全表
        StringBuilder sb = new StringBuilder("(");
        int size = allScans.size();
        if (size == 0) {
            return sql;
        }
        int placeSymbolCnt = getPlaceSymbolCnt(sql);
        int max = allScans.stream().filter(Objects::nonNull)
                .map(AllScan::getCount).reduce((x, y) -> x * y).get() * placeSymbolCnt;
        List<String> sqlList = new ArrayList<>();
        int layerIdx = 0;
        for (int i = 0; i < size; i++) {
            AllScan allScan = allScans.get(i);
            int count = allScan.getCount();
            String prefix = allScan.getPrefix();
            List<String> sqlListCopy = new ArrayList<>(sqlList);
            List<String> tmp = new ArrayList<>();
            for (int j = 0; j < count; j++) {
                if (layerIdx == 0) {
                    // 加空格，避免粘连，比如 tbl_mission 和 tbl_mission_input
                    sqlList.add(sql.replace(prefix + " ", prefix + "_" + (2017 + j + 1) + "   "));
                } else {
                    for (String s : sqlListCopy) {
                        s = s.replace(prefix + " ", prefix + "_" + (2017 + j + 1) + "   ");
                        tmp.add(s);
                    }
                    sqlList = new ArrayList<>(tmp);
                }
                if (parameterMappings.size() < max) {
                    parameterMappings.addAll(parameterMappingsCopy);
                }
            }
            layerIdx++;
        }
        boolean isSelect = false;
        boolean isUpdate = false;
        boolean isDelete = false;
        try {
            MySqlStatementParser parser = new MySqlStatementParser(sql);
            SQLStatement statement = parser.parseStatementList().get(0);
            if (statement instanceof SQLSelectStatement) {
                isSelect = true;
            } else if (statement instanceof SQLUpdateStatement) {
                isUpdate = true;
            } else if (statement instanceof SQLDeleteStatement) {
                isDelete = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (isSelect) {
            for (String s : sqlList) {
                sb.append(s).append(") union all (");
            }
            return sb.substring(0, sb.length() - 13) + ")";
        }
        if (isUpdate || isDelete) {
            for (String s : sqlList) {
                sb.append(s).append(";");
            }
            return sb.substring(0, sb.length() - 1);
        }
        return sql;
    }

    private boolean isScanAll(Map<String, Object> fieldValMap) {
        return fieldValMap.isEmpty();
    }

    private void parseRoute(Map<String, List<String>> needSplitTbl, String tblName,
                            Map<String, Object> fieldValMap, String field, String symbol) {
        // 找不到路由键，
        if (isScanAll(fieldValMap)) {
            needSplitTbl.putIfAbsent(tblName, new ArrayList<>());
            return;
        }
        List<String> targetTblList = new ArrayList<>();
        try (Connection connection = dataSource.getConnection()) {
            if (StrUtil.isBlank(field)) {
                field = new ArrayList<>(fieldValMap.keySet()).get(0);
            }
            Object val = fieldValMap.get(field);
            if (val instanceof Date) {
                val = DateUtil.format((Date) val, "yyyy");
            } else {
                val = ((String) val).substring(0, 4);
            }
            StringBuilder sb = new StringBuilder("select target_tbl from routing_cache  where shard_col='" + field + "'" + " and shard_val");
            if (symbol.equals("in")) {
                sb.append(" in (").append(val).append(")");
            } else if (StrUtil.isNotBlank(symbol)) {
                sb.append(" ").append(symbol).append(" ").append(val);
            } else {
                sb.append(" ").append("=").append(" ").append(val);
            }
            PreparedStatement ps = connection.prepareStatement(
                    sb.toString());
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                String targetTbl = resultSet.getString("target_tbl");
                if (!targetTblList.contains(targetTbl)) {
                    targetTblList.add(targetTbl);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        needSplitTbl.put(tblName, targetTblList);
    }

    private void findFieldValFromSqlProp(List<ParameterMapping> parameterMappingsCopy, List<String> fields, String tblAlias,
                                         SQLExpr where, String finalSql,
                                         Object parameterObj, Map<String, Object> fieldValMap,
                                         Map<String, List<String>> needSplitTbl, String tblName) {

        // 从 sql 里查找 sql 字段是否存在分表键， select 主要从 where 里查,update 从where和set部分查
        if (fieldValMap.isEmpty()) {
            if (where != null) {
                where.accept(new SQLASTVisitorAdapter() {
                    @Override
                    public boolean visit(SQLNotExpr x) {
                        return super.visit(x);
                    }

                    @Override
                    public void endVisit(SQLBinaryOpExpr subExpr) {
                        String opSymbol = subExpr.getOperator().getName();
                        if (subExpr.getLeft() instanceof SQLPropertyExpr) {
                            SQLPropertyExpr left = (SQLPropertyExpr) subExpr.getLeft();
                            if (left.getOwner() == null || !(left.getOwner() instanceof SQLIdentifierExpr)) {
                                super.endVisit(subExpr);
                                return;
                            }
                            String name = ((SQLIdentifierExpr) left.getOwner()).getName() + "." + left.getName();
                            for (String field : fields) {
                                if (name.equalsIgnoreCase(tblAlias + "." + field)) {
                                    findFieldVal(field, tblAlias, finalSql, parameterMappingsCopy, fieldValMap, parameterObj, name, subExpr.toString(), opSymbol);
                                    parseRoute(needSplitTbl, tblName, fieldValMap, field, opSymbol);
                                }
                            }
                        }
                        for (String field : fields) {
                            if (subExpr.getLeft().toString().contains(tblAlias + "." + field)) {
                                System.out.println(subExpr);

                                String name = "";
                                if (subExpr.getLeft() instanceof SQLMethodInvokeExpr) {
                                    List<SQLExpr> arguments = ((SQLMethodInvokeExpr) subExpr.getLeft()).getArguments();
                                    SQLPropertyExpr sqlExpr = (SQLPropertyExpr) arguments.stream().filter(it -> (it.getClass().isAssignableFrom(SQLPropertyExpr.class))).findFirst().orElse(null);
                                    if (sqlExpr != null) {
                                        if (sqlExpr.getOwner() != null && StrUtil.isNotBlank(((SQLIdentifierExpr) sqlExpr.getOwner()).getName())) {
                                            name = ((SQLIdentifierExpr) sqlExpr.getOwner()).getName() + "." + sqlExpr.getName();
                                        } else {
                                            name = sqlExpr.getName();
                                        }

                                    }
                                }
                                findFieldVal(field, tblAlias, finalSql, parameterMappingsCopy, fieldValMap, parameterObj, name, subExpr.toString(), opSymbol);
                                parseRoute(needSplitTbl, tblName, fieldValMap, field, opSymbol);
                            }
                        }

                        super.endVisit(subExpr);
                    }
                });
            }
        }
    }

    private void findFieldValFromParam(Object parameterObj,
                                       List<ParameterMapping> parameterMappingsCopy, List<String> fields, Map<String, Object> fieldValMap) {
        // 寻找 参数名称列表 里是否有该表分表键
        if (!(parameterObj instanceof MapperMethod.ParamMap)) {
            MetaObject metaObject = MetaObject.forObject(parameterObj, DEFAULT_OBJECT_FACTORY,
                    DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_REFLECTOR_FACTORY);
            for (ParameterMapping parameterMapping : parameterMappingsCopy) {
                String property = parameterMapping.getProperty();
                if (fields.contains(property)) {
                    fieldValMap.put(property, metaObject.getValue(property));
                } else if (fields.contains(toSqlProperty(property))) {
                    fieldValMap.put(toSqlProperty(property), metaObject.getValue(property));
                }
            }
            // 是 Map,parameterMap 不能省略
        } else {
            for (String field : fields) {
                MapperMethod.ParamMap<?> paramMap = (MapperMethod.ParamMap<?>) parameterObj;
                if (paramMap.containsKey(field)) {
                    fieldValMap.put(field, paramMap.get(field));
                } else if (paramMap.containsKey(toSqlProperty(field))) {
                    fieldValMap.put(toSqlProperty(field), paramMap.get(field));
                }
            }
        }
    }

    private String toSqlProperty(String property) {
        StringBuilder sb = new StringBuilder();
        char[] chs = property.toCharArray();
        for (char ch : chs) {
            if (ch >= 'A' && ch <= 'Z') {
                sb.append("_").append((char) (ch - 'A' + 'a'));
            } else {
                sb.append(ch);
            }
        }
        return sb.toString();
    }

    private void findFieldVal(String sqlField, String tblAlias, String finalSql, List<ParameterMapping> parameterMappingsCopy,
                              Map<String, Object> fieldValMap, Object finalParameterObj, String leftExpression, String subExpr, String opSymbol) {
        if ("".equals(sqlField)) {
            return;
        }
        boolean isMap = false;
        MapperMethod.ParamMap<?> finalParameterMap = null;
        if (finalParameterObj instanceof MapperMethod.ParamMap) {
            finalParameterMap = (MapperMethod.ParamMap<?>) finalParameterObj;
            isMap = true;
        }
        String field = "".equals(tblAlias) ? sqlField : tblAlias + "." + sqlField;
        if (leftExpression.contains(field)) {
            String property = findVal(finalSql, subExpr, parameterMappingsCopy, opSymbol);
            // 该属性是列表属性,直接拒绝，在 xml 中不是这样使用 '#{别名}'
            if (property.startsWith("__frch_item")) {
                return;
            }
            if (isMap) {
                fieldValMap.put(sqlField, finalParameterMap.get(property));
            } else {
                MetaObject metaObject = MetaObject.forObject(finalParameterObj, DEFAULT_OBJECT_FACTORY,
                        DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_REFLECTOR_FACTORY);
                fieldValMap.put(sqlField, metaObject.getValue(property));
            }
        }
    }


    // 使用 Java 自带的 JS 引擎计算 (1/8+1) 结果
    public int eval(String str) {
        Object res = ScriptUtil.eval(str);
        if (res instanceof Integer) {
            return (int) res;
        }
        Double eval = (Double) res;
        return Math.max(eval.intValue(), 1);
    }


    public String findVal(String sql, String expression, List<ParameterMapping> parameterMappingsCopy, String opSymbol) {
        sql = sql.replaceAll("\\s*" + opSymbol + "\\s*", opSymbol);
        expression = expression.replaceAll("\\s*" + opSymbol + "\\s*", opSymbol);

        int idx = sql.indexOf(expression);
        // 在 expression 前面一共有 count 个 ?
        int count = findCount(sql, idx, "?");
        return parameterMappingsCopy.get(count).getProperty();
    }

    private int findCount(String sql, int end, String pattern) {
        int cnt = 0;
        int idx = sql.indexOf(pattern);
        while (idx >= 0 && idx < end) {
            idx = sql.indexOf(pattern, idx + 1);
            cnt++;
        }
        return cnt;
    }

    private List<AllScan> getAllScan(Map<String, List<String>> needSplitTbl) {
        ArrayList<AllScan> allScans = new ArrayList<>();
        for (Map.Entry<String, List<String>> entry : needSplitTbl.entrySet()) {
            if (entry.getValue().isEmpty()) {
                Sharding annotation = findShardByTblName(entry.getKey());
                allScans.add(new AllScan(annotation.tableNamePrefix(), annotation.length()));
            }
        }
        return allScans;
    }

    private int getPlaceSymbolCnt(String sql) {
        int cnt = 0;
        if (sql == null || sql.length() == 0) {
            return cnt;
        }
        for (int i = 0; i < sql.length(); i++) {
            if (sql.charAt(i) == '?') {
                cnt++;
            }
        }
        return cnt;
    }

    @PostConstruct
    public void initBeanMap() {
        tblNameToBean = new HashMap<>(32);
        Collection<Object> beanList = context.getBeansWithAnnotation(Sharding.class).values();
        for (Object bean : beanList) {
            Sharding annotation = AnnotationUtils.findAnnotation(bean.getClass(), Sharding.class);
            if (annotation != null && annotation.tableNamePrefix().length() > 0) {
                tblNameToBean.put(annotation.tableNamePrefix(), annotation);
            }
        }
    }

    private Sharding findShardByTblName(String tblName) {
        if (tblName == null || tblName.length() == 0) {
            return null;
        }
        return tblNameToBean.get(tblName);
    }

    class BoundSqlSqlSource implements SqlSource {
        private BoundSql boundSql;

        public BoundSqlSqlSource(BoundSql boundSql) {
            this.boundSql = boundSql;
        }

        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }

    }

    private MappedStatement newMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
        MappedStatement.Builder builder = new
                MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.keyGenerator(ms.getKeyGenerator());
        if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
            builder.keyProperty(ms.getKeyProperties()[0]);
        }
        builder.timeout(ms.getTimeout());
        builder.parameterMap(ms.getParameterMap());
        builder.resultMaps(ms.getResultMaps());
        builder.resultSetType(ms.getResultSetType());
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());
        return builder.build();
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
    }

    // 只支持常见计算表达式 (如 id/90+1 ) 或 date_format()
    private String getRouteAddress(String route, Object actualVal, List<String> fields) {
        if (route.contains("date_format")) {
            Pattern pattern = Pattern.compile("date_format\\((.*?)\\)");
            Matcher matcher = pattern.matcher(route);
            int timeResult = 0;
            if (matcher.find()) {
                String content = matcher.group(1);
                String[] times = content.split(",");
                if (actualVal instanceof String) {
                    // "xxxx-xx" 不能格式化
                    times[1] = times[1].replace("`", "");
                    // 一般只按年分表
                    if (times[1].equals("%Y") || times[1].equals("'%Y'")) {
                        timeResult = Integer.parseInt(actualVal.toString().split("-")[0]);
                    }
                }
                route = route.replace(matcher.group(), String.valueOf(timeResult));
            }
        } else {
//            route = route.replace(field, actualVal.toString());
        }
        return String.valueOf(eval(route));
    }


    //            List<String> targetTbl = routingCacheMapper.getTargetTblByShardColAndValue(key, (String) fieldValMap.get(key));
//            needSplitTbl.put(key, targetTbl.get(0));
    // 路由解析
    // 例如 tbl_mission_$->date_format(complete_time,'%Y')-2020
//        String route = routerConfig.getRouteMap().get(prefix);
//        if (StrUtil.isBlank(route) || !route.contains("$")) {
//
//            needSplitTbl.put(tblName, route);
//        } else {
//            String[] config = route.split("->");
//            String routeAddress = getRouteAddress(config[1], fieldValMap, fields);
//            needSplitTbl.put(tblName, prefix + "_" + routeAddress);
//        }


//        if (sqlCommandType == SqlCommandType.DELETE) {
//            Delete delete = (Delete) CCJSqlParserUtil.parse(sql);
//            String tblName = delete.getTable().getName();
//            Sharding annotation = findShardByTblName(tblName);
//            if (annotation != null) {
//                String namePrefix = annotation.tableNamePrefix();
//                List<String> fields = Arrays.asList(annotation.field());
//
//                Map<String, Object> fieldValMap = new HashMap<>();
//                findFieldValFromParam(parameterObj, parameterMappingsCopy, fields, fieldValMap);
//                parseRoute(needSplitTbl, tblName, fieldValMap);
//                sql = genSql(sql, parameterMappings, parameterMappingsCopy, needSplitTbl, 0, false);
//            }
//        }
}
